#!/usr/bin/env python3

# Copyright (c) 2020-2021 Fpemud <fpemud@sina.com>
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:
#
# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.
#
# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
# THE SOFTWARE.


import os
import json
import sqlite3
from ._util import Util
from ._const import Const
from ._errors import DiskCacheError


class DiskCache:

    def __init__(self, cfg, bSysOrUser, bReadOnly):
        self._cfg = cfg
        self._bSysOrUser = bSysOrUser
        self._bReadOnly = bReadOnly
        self._dbConn = None

    def getNeedUpdateBackendNameMirrorNamesDict(self, dtNow, backendDict, mirrorNameList):
        if len(backendDict) == 0:
            return {}

        self._ensureDbConn()
        dtNowInSecond = int(dtNow.timestamp())

        with self._dbConn:
            needUpdateBackendDict = {x: None for x in backendDict.keys()}

            # filter backend by last_full_update_time
            if True:
                s = " UNION ALL ".join([f"SELECT '{x}' AS backend" for x in backendDict.keys()])
                c = self._dbConn.execute(f'''
                    WITH
                        _filter AS (
                            {s}
                        )
                    SELECT b.backend
                        FROM temp.backend_data AS b
                        JOIN _filter AS f ON b.backend = f.backend
                        WHERE b.last_full_update_time IS NOT NULL AND b.last_full_update_time + b.aging_time >= ?
                ''', (dtNowInSecond,))
                row = c.fetchone()
                while row is not None:
                    del needUpdateBackendDict[row[0]]
                    row = c.fetchone()

            # filter backend and mirror names by mirror's last_update_time
            if mirrorNameList is not None:
                for backendName in needUpdateBackendDict.keys():
                    needUpdateBackendDict[backendName] = list(mirrorNameList)

                s = None
                if True:
                    tlist = []
                    for backendName in needUpdateBackendDict.keys():
                        for mirrorName in mirrorNameList:
                            tlist.append((backendName, mirrorName))
                    s = " UNION ALL ".join([f"SELECT '{x}' AS backend, '{y}' AS mirror_name" for x, y in tlist])
                c = self._dbConn.execute(f'''
                    WITH
                        _filter AS (
                            {s}
                        )
                    SELECT m.backend, m.mirror_name
                        FROM temp.mirror_data AS m
                        JOIN temp.backend_data AS b ON m.backend = b.backend
                        JOIN _filter AS f ON m.backend = f.backend AND m.mirror_name = f.mirror_name
                        WHERE m.last_update_time + b.aging_time >= ?
                ''', (dtNowInSecond,))
                row = c.fetchone()
                while row is not None:
                    needUpdateBackendDict[row[0]].remove(row[1])
                    row = c.fetchone()

                for backendName, mirrorNameList in list(needUpdateBackendDict.items()):
                    if len(mirrorNameList) == 0:
                        del needUpdateBackendDict[backendName]

            return needUpdateBackendDict

    def update(self, dtNow, backendDataDict, backendAgingTimeDict, mirrorNameList):
        if len(backendDataDict) == 0:
            return

        self._ensureDbConn()
        dtNowInSecond = int(dtNow.timestamp())

        with self._dbConn:
            if mirrorNameList is None:
                for backendName, v in backendDataDict.items():
                    # remove all mirror data entry belongs this backend with or without data
                    self._dbConn.execute('''
                        DELETE FROM main.mirror_data
                            WHERE backend = ?
                    ''', (backendName,))
                    # save mirror data
                    for mirrorName, data in v.items():
                        self._dbConn.execute('''
                            INSERT INTO main.mirror_data
                                (backend, mirror_name, data, last_update_time)
                                VALUES (?, ?, ?, ?)
                        ''', (backendName, mirrorName, json.dumps(data), dtNowInSecond))
                    # save aging-time, last-full-update-time
                    self._dbConn.execute('''
                        INSERT OR REPLACE INTO main.backend_data
                            (backend, aging_time, last_update_time, last_full_update_time)
                            VALUES (?, ?, ?, ?)
                    ''', (backendName, backendAgingTimeDict[backendName], dtNowInSecond), dtNowInSecond)
            else:
                for backendName, v in backendDataDict.items():
                    # save mirror data entry with or without data
                    for mirrorName in mirrorNameList:
                        data = json.dumps(v[mirrorName]) if mirrorName in v else None
                        self._dbConn.execute('''
                            INSERT OR REPLACE INTO main.mirror_data
                                (backend, mirror_name, data, last_update_time)
                                VALUES (?, ?, ?, ?)
                        ''', (backendName, mirrorName, data, dtNowInSecond))
                    # save aging-time
                    self._dbConn.execute('''
                        INSERT INTO main.backend_data
                            (backend, aging_time, last_update_time, last_full_update_time)
                            VALUES (?, ?, ?, NULL)
                            ON CONFLICT(backend)
                                DO UPDATE SET
                                    aging_time = excluded.aging_time,
                                    last_update_time = excluded.last_update_time
                    ''', (backendName, backendAgingTimeDict[backendName], dtNowInSecond))

    def getMirrorNames(self, dtNow, backendDict):
        if len(backendDict) == 0:
            return []

        self._ensureDbConn()
        dtNowInSecond = int(dtNow.timestamp())

        c = self._dbConn.execute('''
            SELECT DISTINCT m.mirror_name
                FROM temp.mirror_data AS m
                JOIN temp.backend_data AS b ON m.backend = b.backend
                WHERE m.data IS NOT NULL AND m.last_update_time + b.aging_time >= ?
        ''', (dtNowInSecond,))
        return [row[0] for row in c.fetchall()]

    def getData(self, dtNow, backendDict, mirrorNameList):
        if len(backendDict) == 0:
            return {}

        self._ensureDbConn()
        dtNowInSecond = int(dtNow.timestamp())

        s = self._getSelectSeqSql(backendDict)
        s2 = " UNION ALL ".join([f"SELECT '{x}' AS mirror_name" for x in mirrorNameList])
        c = self._dbConn.execute(f'''
            WITH
                _seq AS (
                    {s}
                ),
                _filter AS (
                    {s2}
                )
            SELECT m.backend, m.mirror_name, m.data
                FROM temp.mirror_data AS m
                JOIN temp.backend_data AS b ON m.backend = b.backend
                JOIN _seq AS t ON m.backend = t.backend
                JOIN _filter AS f ON m.mirror_name = f.mirror_name
                WHERE m.data IS NOT NULL AND m.last_update_time + b.aging_time >= ?
                ORDER BY t.seq DESC                                                     -- reverse order, so that we can override site data
        ''', (dtNowInSecond,))

        ret = {}
        if True:
            row = c.fetchone()
            while row is not None:
                if row[1] not in ret:
                    ret[row[1]] = {
                        "sources": [],
                        "mirrors": [],
                    }
                tdata = json.loads(row[2])
                if "sources" in tdata:
                    ret[row[1]]["sources"] = tdata["sources"]         # FIXME: should merge, not override
                if "mirrors" in tdata:
                    ret[row[1]]["mirrors"] = tdata["mirrors"]         # FIXME: should merge, not override
                row = c.fetchone()
        return ret

    def clean(self):
        assert not self._bReadOnly
        self._ensureDbConn()

        with self._dbConn:
            # remove all data belongs to backends that no longer exist
            s = " UNION ALL ".join([f"SELECT '{x}' AS backend" for x in self._cfg.get_backend_dict().keys()])
            self._dbConn.execute(f'''
                WITH
                    _filter AS (
                        {s}
                    )
                DELETE FROM mirror_data WHERE backend NOT IN _filter
            ''')

            # remove outdated data
            # FIXME

            # remove all data in user cache that there is newer data in system cache
            if not self._bSysOrUser:
                # FIXME
                pass

    def _ensureDbConn(self):
        if self._dbConn is not None:
            return self._dbConn

        while True:
            cacheDir = Const.getCacheDir(self._bSysOrUser)
            if not os.path.exists(cacheDir):
                if self._bReadOnly:
                    conn = sqlite3.connect("file::memory:?mode=ro", uri=True)
                    conn.executescript(self._getCreateTableSql())
                    break
                os.makedirs(cacheDir)
            # FIXME: should ensure mode
            pass

            fullfn = os.path.join(cacheDir, Const.CACHE_FILENAME)
            if os.path.exists(fullfn):
                conn = sqlite3.connect(fullfn)
                if Util.sqliteCheckTables(conn, self._getCreateTableSql()):
                    break
                else:
                    conn.close()
                    if self._bReadOnly:
                        raise DiskCacheError("invalid database file \"%s\"" % (fullfn))
                    Util.forceDelete(fullfn)
                    if self._bReadOnly:
                        conn = sqlite3.connect("file::memory:?mode=ro", uri=True)
                        conn.executescript(self._getCreateTableSql())
                        break
            else:
                if self._bReadOnly:
                    conn = sqlite3.connect("file::memory:?mode=ro", uri=True)
                    conn.executescript(self._getCreateTableSql())
                    break

            conn = sqlite3.connect("file:%s%s" % (fullfn, "?mode=ro" if self._bReadOnly else ""), uri=True)
            conn.executescript(self._getCreateTableSql())
            break

        # parent database and union-view
        # SELECT should happen in temp.*
        while True:
            if not self._bSysOrUser:
                fullfn = Const.getCacheFileFullfn(True)
                if os.path.exists(fullfn):
                    conn.execute("ATTACH DATABASE '%s' AS sys" % (fullfn))
                    conn.executescript('''
                        CREATE TEMP VIEW mirror_data AS
                            SELECT backend, mirror_name, data, last_update_time
                                FROM (
                                    SELECT backend, mirror_name, data, last_update_time,
                                        ROW_NUMBER() OVER (
                                            PARTITION BY backend, mirror_name
                                            ORDER BY last_update_time DESC
                                        ) AS rn
                                        FROM (
                                            SELECT * FROM sys.mirror_data
                                            UNION ALL
                                            SELECT * FROM main.mirror_data
                                        )
                                )
                                WHERE rn = 1;
                        CREATE TEMP VIEW backend_data AS
                            SELECT backend,
                                   aging_time,
                                   last_update_time,                                                                    -- use line with max last_update_time
                                   MAX(last_full_update_time) OVER (PARTITION BY backend) AS last_full_update_time      -- but always using max last_full_update_time
                                FROM (
                                    SELECT backend, aging_time, last_update_time, last_full_update_time,
                                        ROW_NUMBER() OVER (
                                            PARTITION BY backend
                                            ORDER BY last_update_time DESC
                                        ) AS rn
                                        FROM (
                                            SELECT * FROM sys.backend_data
                                            UNION ALL
                                            SELECT * FROM main.backend_data
                                        )
                                )
                                WHERE rn = 1;
                    ''')
                    break

            conn.executescript('''
                CREATE TEMP VIEW mirror_data AS SELECT * FROM main.mirror_data;
                CREATE TEMP VIEW backend_data AS SELECT * FROM main.backend_data;
            ''')
            break

        self._dbConn = conn
        return self._dbConn

    @staticmethod
    def _getSelectSeqSql(backendDict):
        return " UNION ALL ".join(["SELECT '%s' AS backend, %d AS seq" % (v[0], i) for i, v in enumerate(backendDict.items())])

    @staticmethod
    def _getCreateTableSql():
        return '''
            CREATE TABLE mirror_data (
                backend TEXT NOT NULL,
                mirror_name TEXT NOT NULL,
                data TEXT,
                last_update_time INTEGER NOT NULL,      -- as unix epoch, unit: second
                PRIMARY KEY (backend, mirror_name)
            );
            CREATE TABLE backend_data (
                backend TEXT NOT NULL,
                aging_time INTEGER NOT NULL,            -- unix: second
                last_update_time INTEGER NOT NULL,      -- as unix epoch, unit: second
                last_full_update_time INTEGER,          -- as unix epoch, unit: second
                PRIMARY KEY (backend)
            );
        '''
